/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.TeacherDTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Arturia
 */
public class TeacherDAO {

    public static String generateID(Connection con) {
        String id = null;
        try {
            String sql = "SELECT MAX(numericID) "
                    + "FROM (SELECT 'numericID' = "
                    + "CASE WHEN ISNUMERIC(teacher_id) = '1' THEN CAST(teacher_id AS INT) "
                    + "ELSE NULL END FROM t_teacher) ALIAS";
            PreparedStatement pre = con.prepareStatement(sql);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                int iID = Integer.parseInt(id = rs.getString(1));
                id = String.valueOf(iID + 1);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return id;
    }

    public static ArrayList LoadDatabase(Connection con, TeacherDTO teacher) {
        ArrayList<TeacherDTO> result = new ArrayList<TeacherDTO>();
        String sql = "";
        PreparedStatement stm = null;
        try {
            if (teacher == null) {
                sql = "SELECT * FROM t_teacher";
                stm = con.prepareStatement(sql);
            }

            ResultSet rs = stm.executeQuery();
            while (rs.next()) {
                TeacherDTO teacherTemp = new TeacherDTO();
                teacherTemp = new TeacherDTO();

                teacherTemp.setStrTeacherID(rs.getString(1));

                if (rs.wasNull()) {
                    teacherTemp.setStrTeacherID("");
                }

                teacherTemp.setStrPersonID(rs.getString(2));

                if (rs.wasNull()) {
                    teacherTemp.setStrPersonID(generateID(con));
                }


                result.add(teacherTemp);
            }
            stm.close();
            con.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return result;
    }

    public static int insert(Connection con, TeacherDTO teacher) {
        int result = 0;
        try {
            String sql = "INSERT INTO t_teacher VALUES(?,?)";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, teacher.getStrTeacherID());
            stm.setString(2, teacher.getStrPersonID());
            result = stm.executeUpdate();

            stm.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static int update(Connection con, TeacherDTO teacher) {
        int result = 0;
        try {
            String query = "UPDATE t_teacher SET person_id=?"
                    + "WHERE teacher_id=?";
            PreparedStatement stm = con.prepareStatement(query);
            stm.setString(1, teacher.getStrPersonID());
            stm.setString(2, teacher.getStrTeacherID());

            result = stm.executeUpdate();
            stm.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public static int delete(Connection con, String teacherID) {
        int result = 0;
        try {
            String sql = "DELETE FROM t_teacher WHERE teacher_id=?";
            PreparedStatement stm = con.prepareStatement(sql);
            stm.setString(1, teacherID);
            result = stm.executeUpdate();
            stm.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }
}
